/*
 * FuncConstr.java - SQL operations with the table 'func_constr'
 *  in the database 'context' (copy of WebDeso).
 *
 * Copyright (c) 2007 Computer-Aided Integrated Systems Laboratory, 
 * St. Petersburg Institute for Informatics and Automation RAS http://cais.lisa.iias.spb.su
 */

package dbcontext;
import dbindex.Language;

import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/** Routines to work with the table 'func_constr' in the database 'context'.
 */
public class FuncConstr {
    
    /** Functional relation's identifier, =WDFunc.iIDFromDB*/
    //private int      func_id;
    
    /** id, title (name), and description of functional relation in the table 'term' */
    private TermTable term;
    
    /** id, title (name), and description of output attribute in the table 'attr' */
    private AttrTable    attr_out;
    
    /** Type of constraint: 0 - attribute belonging to class, 1 - hierarchy, 2 - associated*/
    private int relation_type;
    /** Relation: attribute-class, or hierarchy, or association, it depends on
     * relation_type */
    RelationTable relation;
    
    /** ID from Class/Hier/Acon (associative). 
     * 
     * Attention: this ID of relation (in 'context' database) 
     * <> ID of relation in 'webdeso' database. */
    //private int relation_id; == relation.id
    
    /**	0 - method, 1 - constant */
    private boolean     func_type;
    /** Constant or class ID, it depends on func_type */
    private int         func_value;
    /** Null or class, it depends on func_type */
    private ClassTable  class_value;
    
    /**  0 = ; 1 > ; 2 < */
    private int sign;
    // private boolean bIsInherited;    //  Is inherited?
    // private int iFatherClassID;      //  Class ID the relation is inherited from
    
    //static private String SQL_PREPARED_SELECT_STATEMENT_WDFunc = "SELECT term_id AS termid, attrout_id AS attroutid, 
    // relation_type AS relationtype, relation_id AS relationid, func_type AS functype, func_value AS funcvalue, sign, id AS idfromdb 
    // FROM func_constr WHERE func_type = 0 AND  relation_type = 0 AND  attrout_id IN ( SELECT id From attr WHERE onto_id = ? )";
    
    /** Creates a new instance of FuncConstr */
    public FuncConstr() {
    }
    
    
    /** Inserts element to the table 'func_constr' in MySQL database.<br>
     *
     * Intelligence of the function is the translation of relation_id_in_webdeso
     * to relation_id in 'context' database.
     * 
     * SQL example:
     * attribute term
     * INSERT INTO term (lang_id, text, description) VALUES (0, "func_attr12", "func_attr_desc12")
     * SELECT * FROM term WHERE text="func_attr12"
     * id=134039
     *
     * function term
     * INSERT INTO term (lang_id, text, description) VALUES (0, "func12", "func_desc12");
     * SELECT * FROM term WHERE text="func12"
     * id=134048
     *
     * class-attr relation
     * relation_type=0, int relation_id_in_webdeso
     * INSERT INTO class_attr (class_id,attr_id) VALUES(41,31)
     * SELECT id FROM class_attr WHERE class_id=41 AND attr_id=31
     * 41764
     *
     * class (func_type=0 (method),func_value=id of class) 
     * INSERT INTO term (lang_id, text, description) VALUES (0, "class_value_title12", "class_value_desc12")
     * SELECT id FROM term WHERE text="class_value_title12"
     * 134040
     * INSERT INTO class (onto_id,base_onto_id,id,parent_id,term_id) VALUES(101,102,41,42,134040)
     * SELECT id FROM class WHERE term_id=134040
     *
     * // main
     * INSERT INTO func_constr (term_id,attrOut_id,relation_type,relation_id,func_type,func_value,Sign) VALUES(134048,134039,0,41764,0,41,0)<br>
     * UPDATE func_constr SET attrOut_id=134039,relation_type=0,relation_id=41764,func_type=0,func_value=41,Sign=0 WHERE term_id=134048
     *
     * DELETE FROM func_constr WHERE term_id=134048
     *
     *  @param term_id_webdeso  id of relation title in the table 'term' in the 
     *                          database 'webdeso'
     *  @param class_desc       description of class (stored in the table term)
     *  @param term_id_webdeso  id of function name in webdeso.term.title, 
     *                          Attention, it is supposed that all functions 
     *                          have unique term_id.
     *  @return true if there are no errors
     */
    public static boolean insert ( java.sql.Connection webdeso_conn, java.sql.Connection context_conn, Language lang,
                                int term_id_webdeso,
                                int attr_out_id_webdeso,
                                int relation_type, int relation_id_in_webdeso,
                                int func_type,  int func_value, int sign) {
        // FuncConstr.insert(webdeso_conn, context_conn, lang, iAttrOutID, iRelationType, iRelationID, iFuncType, iFuncValue);
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        TermTable term_func_from_webdeso = TermTable.getByID(webdeso_conn, term_id_webdeso);
        if(null == term_func_from_webdeso) {
            System.out.println("Error in context.FuncConstr.java insert()):: there is no term in webdeso with id="+term_id_webdeso);
            return false;
        }
        int term_id = TermTable.insertAndGetID( context_conn, lang, 
                                                term_func_from_webdeso.text, 
                                                term_func_from_webdeso.desc);
        
        // now webdeso.attr.id === context.attr.id 
        AttrTable attr_out = AttrTable.getByAttrID(context_conn, attr_out_id_webdeso); 
        if(null == attr_out) {
            System.out.println("Error in context.FuncConstr.java insert()):: there is no attribute in the table 'attr' with id="+attr_out_id_webdeso);
            return false;
        }
        
        ClassTable  class_value=null;
        // 0 - method, 1 - constant
        if(0 == func_type) {                                    // Constant or class ID, it depends on func_type
            class_value = ClassTable.getByClassID(context_conn, func_value);
            if(null == class_value) {
                System.out.println("Error in context.FuncConstr.java insert()):: "+
                        "0=func_type, i.e. func_value is ID of class in 'context' func_value="+func_value+
                        " but it is absent in the table 'class'.");
                return false;
            }
        }
        
        // gets relation by (int relation_type, int relation_id_in_webdeso)
        RelationTable r = RelationTable.getByMovingFromWebdesoToContext 
                                (webdeso_conn, context_conn,
                                relation_type, relation_id_in_webdeso);
        
        if(null == r) {
            System.out.println("Error in context.FuncConstr.java insert()):: there is no relation with relation_type="+relation_type+
                    " (0 - attribute belonging to class, 1 - hierarchy, 2 - associated), relation_id="+relation_id_in_webdeso);
            return false;
        }
        if(r.id <= 0) {
            System.out.println("Error in context.FuncConstr.java insert()):: ID of relation <=0, relation_type="+relation_type+
                    " (0 - attribute belonging to class, 1 - hierarchy, 2 - associated), id="+r.id);
            return false;
        }
        
        try
        {   
            s = context_conn.createStatement ();
            
            // IF there is no record with (term_id) in table 'func_constr' in db 'context'
            //     then insert 
            //   else update WHERE term_id 
            // ENDIF
            
            FuncConstr f = getByTermID(context_conn, term_id);
            if (f != null)
            {   // UPDATE
                str_sql = "UPDATE func_constr SET" + 
                                " attrOut_id="      + attr_out_id_webdeso +  // now webdeso.attr.id === context.attr.id 
                                ",relation_type="   + relation_type + 
                                ",relation_id="     + r.id          +
                                ",func_type="       + func_type     + 
                                ",func_value="      + func_value    + 
                                ",Sign="            + sign          + 
                          " WHERE term_id="         + term_id;
            } else {
                // INSERT
                str_sql = "INSERT INTO func_constr (term_id,attrOut_id,relation_type,relation_id,func_type,func_value,Sign) "+ 
                                "VALUES (" + term_id    + 
                                ","+ attr_out_id_webdeso + 
                                ","+ relation_type      + 
                                ","+ r.id               +
                                ","+ func_type          +
                                ","+ func_value         +
                                ","+ sign               + ")";
            }
            s.executeUpdate (str_sql);
        } catch(SQLException ex) {
            System.err.println("SQLException (context.FuncConstr.java insert()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        return true;
    }
    
    
    /** Selects data from table 'func_constr', if there are 
     *      (1) term with term_id, 
     *      (2) pair of ontology classes parent-child in the table 'hierarchy', 
     *          or associative, or class-term relations,
     *      (3) class with id=func_value (in the table 'class'), if 0=func_type
     *
     *  SQL:
     *  SELECT * FROM func_constr WHERE term_id=134048
     *  
     *  @return FuncConstr object with initialized fields
     */
    public static FuncConstr getByTermID (java.sql.Connection conn,int term_id) {
        
        FuncConstr  f = null;
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        TermTable term = TermTable.getByID(conn, term_id);
        if(null == term) {
            System.out.println("Error in context.FuncConstr.java getByTermID()):: " +
                    "there is no term with id=" + term_id + " in the table 'term'.");
            return null;
        }
        
        try {
            s = conn.createStatement ();
            str_sql = "SELECT * FROM func_constr WHERE term_id=" + term_id;
            s.executeQuery (str_sql);
            
            rs = s.getResultSet ();
            if (rs.next ())
            {   
                int attr_out_id = rs.getInt("attrOut_id");
                AttrTable attr_out = AttrTable.getByAttrID(conn, attr_out_id);
                if(null == attr_out) {
                    System.out.println("Error in context.FuncConstr.java getByTermID()):: there is no attribute in the table 'attr' with id="+attr_out_id);;
                } else {
                    
                    int func_type  = rs.getInt("func_type");
                    int func_value = rs.getInt("func_value");
                    ClassTable  class_value=null;
                    // 0 - method, 1 - constant
                    if(0 == func_type) {                                    // Constant or class ID, it depends on func_type
                        class_value = ClassTable.getByClassID(conn, func_value);
                    }
                    if(0 == func_type && null == class_value) {
                        System.out.println("Error in context.FuncConstr.java getByTermID()):: "+
                                "0=func_type, i.e. func_value is ID of class in 'context' func_value="+func_value+
                                ", but it is absent in the table 'class'.");
                    } else {
                        
                        int relation_type = rs.getInt("relation_type");
                        int relation_id   = rs.getInt("relation_id"  );
                        RelationTable r = RelationTable.getRelation(conn, relation_type, relation_id);

                        if(null == r) {
                            System.out.println("Error in context.FuncConstr.java getByTermID()):: there is no relation with relation_type="+relation_type+
                                    " (0 - attribute belonging to class, 1 - hierarchy, 2 - associated), relation_id="+relation_id);
                        } else {
                            if(r.id <= 0) {
                                System.out.println("Error in context.FuncConstr.java insert()):: ID of relation <=0, relation_type="+relation_type+
                                        " (0 - attribute belonging to class, 1 - hierarchy, 2 - associated), id="+r.id);
                            } else {
                                f = new FuncConstr ();
                                f.term          = term;
                                f.attr_out      = attr_out;
                                f.func_type     = (1 == func_type) ? true : false;
                                f.class_value   = class_value;
                                f.relation      = r;
                                f.relation_type = relation_type;
                                f.sign          = rs.getInt("sign");
                                assert(f.relation.id == relation_id);
                            }
                        }
                    }
                }
            }
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.FuncConstr.java get()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
        return f;
    }
    
    
    /** Deletes functional constraint row by term id (name of function).
     *  SQL:        
     *  DELETE FROM func_constr WHERE term_id=134048
     */
    public static void delete (java.sql.Connection conn,int term_id) {
        
        Statement   s = null;
        ResultSet   rs= null;
        String      str_sql = null;
        
        try {
            s = conn.createStatement ();
            str_sql = "DELETE FROM func_constr WHERE term_id=" + term_id;
            s.execute(str_sql);
            
        } catch(SQLException ex) {
            System.err.println("SQLException (Context.FuncConstr.java delete()):: sql='" + str_sql + "' " + ex.getMessage());
        } finally {
            if (rs != null) {   try { rs.close(); } catch (SQLException sqlEx) { }  rs = null; }
            if (s != null)  {   try { s.close();  } catch (SQLException sqlEx) { }  s = null;  }
        }
    }
    
}
